﻿using System;
using System.Configuration;
using System.Data.OleDb;
using Business.Compta;
using System.Collections.Generic;
using System.IO;

namespace DAO.Compta
{
    public class ComptaStatementsDAO
    {
        public static List<ComptaStatement> getStatementsForAccount(int idAccount)
        {
            List<ComptaStatement> _return = new List<ComptaStatement>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();


            OleDbCommand myOleDbCommand2 = new OleDbCommand("select ID, TITLE,  FUNCTIONNAL_DATE, IMPORT_DATE, FILE_SIZE FROM COMPTA_FILES where ID_ACCOUNT = @idAccount",
                conn);
            myOleDbCommand2.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAccount;

            OleDbDataReader dr = myOleDbCommand2.ExecuteReader();

            int id, fileSize;
            string title;
            DateTime importDate, functionnalDate;
            while (dr.Read())
            {
                id = dr.GetInt32(0);
                title = dr.IsDBNull(1) ? null : dr.GetString(1);
                functionnalDate = dr.GetDateTime(2);
                importDate = dr.GetDateTime(3);
                fileSize = dr.GetInt32(4);

                _return.Add(new ComptaStatement(id,idAccount,title,functionnalDate,importDate,fileSize));
            }
            dr.Close();
            conn.Close();
            return _return;
        }

        public static byte[]  getStatement(int idStatement)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2 = new OleDbCommand("select FILE FROM COMPTA_FILES where ID = @idStatement",
                conn);
            myOleDbCommand2.Parameters.Add("@idStatement", OleDbType.Integer).Value = idStatement;

            OleDbDataReader dr = myOleDbCommand2.ExecuteReader();

            while (dr.Read())
            {
                return (byte[])dr.GetValue(0);
            }
            dr.Close();
            conn.Close();
            return null;
        }

        public static int InsertStatement(ComptaStatement comptaStatement, Stream file)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into COMPTA_FILES(ID_ACCOUNT,FILE,TITLE, FUNCTIONNAL_DATE, IMPORT_DATE, FILE_SIZE) VALUES "+
                "(@idAccount, @file, @title, @functionnalDate, @importDate, @fileSize)";
            comptaStatement.ImportDate = DateTime.Now;
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = comptaStatement.IdAccount;
            myCommand.Parameters.Add("@file", OleDbType.Binary).Value = MainDAO.ReadFully(file);
            myCommand.Parameters.Add("@title", OleDbType.VarChar).Value = comptaStatement.Title;
            myCommand.Parameters.Add("@functionnalDate", OleDbType.Date).Value = comptaStatement.Date;
            myCommand.Parameters.Add("@importDate", OleDbType.Date).Value = comptaStatement.ImportDate;
            myCommand.Parameters.Add("@fileSize", OleDbType.Integer).Value = comptaStatement.FileSize;

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int DeleteStatement(ComptaStatement statement)
        {
            int _return = -1;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from COMPTA_FILES where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = statement.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }
    }
}
